The Third Normal Form (3NF) is a further step in the normalization process that eliminates transitive dependencies in a table. 3NF ensures that non-key attributes are directly dependent on the primary key and not indirectly dependent through another non-key attribute. By achieving 3NF, we reduce redundancy even further, making the database more efficient and consistent.

Note: For a table to be in 3NF, it must first be in 2NF.

Requirements of 3NF

To satisfy the requirements of 3NF:

1. The table must already be in 2NF.
2. There should be no transitive dependencies, meaning non-key attributes should not depend on other non-key attributes.

Example: Student_Department Table

Consider the following Student_Department table, which records information about students, their departments, and department locations. Here, Student_ID is the primary key.

In this table:

• Student_ID is the primary key.
• Department_ID uniquely determines Department_Name and Department_Location, so we have transitive dependencies:
  • Student_ID → Department_ID
  • Department_ID → Department_Name and Department_ID → Department_Location

Because Department_Name and Department_Location depend on Department_ID (a non-key attribute) instead of directly on Student_ID, this table violates 3NF due to transitive dependencies.

Converting to 3NF

To bring this table into 3NF, we need to remove the transitive dependencies by separating the table into two tables: Student and Department. Here’s how we can structure them:

Step 1: Create the Student Table

The Student table will store information specific to each student, with Student_ID as the primary key.

Step 2: Create the Department Table

The Department table will store department-related information, with Department_ID as the primary key.

Result After Conversion to 3NF

After separating the original table into Student and Department tables:

• The Student table now contains only the attributes directly related to students.
• The Department table contains department-related attributes.
• The transitive dependency has been removed, as Department_Name and Department_Location are now in a separate table directly dependent on Department_ID.

Why 3NF is Important

3NF helps:

• Reduce Redundancy: By eliminating transitive dependencies, we avoid repeating information (e.g., department location).
• Improve Data Integrity: Updates become more manageable, as each piece of data is stored in only one place.
• Simplify Maintenance: Changes in one table (e.g., updating department location) don’t require updates in multiple rows, reducing the chance of errors.

In the next lesson, we’ll discuss Boyce-Codd Normal Form (BCNF), an advanced form of 3NF that resolves additional dependencies, ensuring a higher level of normalization.